﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Text;
using System.Net;
using System.Net.Sockets;
namespace LHWebApp
{
    public class LngLat
    {
        public double Lng { get; set; }
        public double Lat { get; set; }
    }
    /// <summary>
    ///Services 的摘要说明
    /// </summary>
    public class Services
    {

        //2019.09.20 zx
        public static DataSet GetVehicleCurrentInfoList(string service, string clientNo, string licensePlate, string carGroup, string status, string carType)
        {
            String url;
            if (service == "6")
            {
                url = string.Format("exec GetWorkerToiletCurrentInfoList '{0}'", clientNo);
                SqlConnection cn = new SqlConnection(ConnectionString);
                SqlCommand cm = new SqlCommand(url, cn);

                cm.Connection.Open();

                SqlDataAdapter adapter = new SqlDataAdapter(cm);
                DataSet ds = new DataSet();
                adapter.Fill(ds);
                return ds;
            }
            else if (service == "5")
            {
                if (status == "1")
                {
                    status = "";
                }
                if (status == "0")
                {
                    status = "在线";
                }
                if (status == "6")
                {
                    status = "离线";
                }
                url = string.Format("Exec GetCurrentInfoListAll '{0}','{1}','{2}','{3}','{4}'", clientNo, licensePlate, carGroup, status, carType);
                return GetData(url);
            }
            
            if (carGroup.Length != 0)
            {
                clientNo = carGroup;
            }

            url = string.Format("Exec WebGetVehicleCurrentInfoListNew '{0}','{1}','{2}','{3}','{4}'", clientNo, licensePlate, "", status, carType);
            if (service == "3") {
                url = string.Format("Exec WebGetVehicleCurrentInfoListNewRubbish '{0}','{1}','{2}','{3}','{4}'", clientNo, licensePlate, "", status, carType);
            }
            return GetData(url);
        }

        public static DataSet GetVehicleCurrentInfoCity(string service, string clientNo)//实时在线统计 全市与滨海新区
        {
            String url = string.Format("Exec WebGetVehicleCurrentInfo '{0}'", clientNo);
            if (service == "3") {
                url = string.Format("Exec WebGetVehicleCurrentInfoRubbish '{0}'", clientNo);
            }
            if (service == "5") {
                url = string.Format("Exec GetWorkerStatisticsInfoAll '{0}'", clientNo);
            }
            return GetData(url);
        }

        public static DataSet GetVehicleCurrentInfoDistrict(string service, string clientNo)//实时在线统计 区级
        {
            String url = string.Format("Exec WebGetVehicleCurrentInfoByClientno '{0}'", clientNo);
            if (service == "3") {
                url = string.Format("Exec WebGetVehicleCurrentInfoByClientnoRubbish '{0}'", clientNo);
            }
            if (service == "5") {
                url = string.Format("Exec GetWorkerStatisticsInfoAll '{0}'", clientNo);
            }
            return GetData(url);
        }

        public static DataSet GetVehicleCurrentInfoSynthetical(string clientNo, string tab)//实时在线统计 区级
        {
            //EXEC GetWorkerStatisticsInfoAll ‘0000004’,’环卫队’
            String url = string.Format("Exec GetWorkerStatisticsInfoAll '{0}','{1}'", clientNo, tab);
            return GetData(url);
        }



        public static DataSet GetClientVehicleNodeList(string service, string clientNo)//左边树装表
        {
            String url = string.Format("Exec WebGetClientVehicleTree '{0}'", clientNo);
            if (service == "3")
            {
                url = string.Format("Exec WebGetClientVehicleTreeRubbish '{0}'", clientNo);
            }
            if (service == "5")
            {
                url = string.Format("Exec WebGetClientVehicleTreeALL '{0}'", clientNo);
            }
            return GetData(url);
        }

        public static DataSet GetVehicleHistoryInfoList(string startDate, string endDate, int vid)//历史轨迹
        {
            return GetData(string.Format("Exec WebGetVehicleHistoryInfoList '{0}','{1}',{2}", startDate, endDate, vid));
        }

        public static DataSet GetVideoBySimcard_No(string carNum)//实时监控视频 用户名密码
        {
            return GetData(string.Format("Exec S_GetIccidBySimcard_No '{0}'", carNum));
        }

        public static DataSet GetVideoByLoaction(string lng, string lat)//实时监控视频 用户名密码
        {
            return GetData(string.Format("Exec S_GetIccidByLoaction '{0}','{1}'", lng, lat));
        }

        public static DataSet GetVideoLoginInfo(string clientNo)//获取视频账号登录信息//此接口无需适配各个作业系统
        {
            return GetData(string.Format("Exec WebGetVideoLoginInfo '{0}'", clientNo));
        }

        /// <summary>
        /// 获取组织机构//此接口无需适配各个作业系统
        /// </summary>
        /// <param name="rootID"></param>
        /// <returns></returns>
        public static DataSet GetClientInfoList(string service, string rootID)
        {
            DataSet ds = new DataSet("Article");

            // 从数据库获取数据，放入 record 表
            string strSql = string.Format("EXEC GetClientListByRootID '{0}'", rootID);
            if (service == "3")
            {
                strSql = string.Format("EXEC GetClientListByRootIDRubbish '{0}'", rootID);
            }
            if (service == "5")
            {
                strSql = string.Format("EXEC GetClientListByRootIDAll '{0}'", rootID);
            }
            if (service == "6")
            {
                strSql = string.Format("EXEC GetClientListByRootIDToilet '{0}'", rootID);
            }

            SqlDataAdapter da = new SqlDataAdapter(strSql, ConnectionString);
            DataTable dtRecord = new DataTable("record");
            lock (da)
            {
                da.Fill(dtRecord);
            }
            ds.Tables.Add(dtRecord);

            // 计算总行数，放入 results 表
            DataTable dtResult = new DataTable("results");
            dtResult.Columns.Add("totalRecords");
            DataRow dr = dtResult.NewRow();
            return ds;
        }
        /// <summary>
        /// 获取车辆列表 报警列表
        /// </summary>
        /// <param name="rootID"></param>
        /// <returns></returns>
        public static DataSet GetVehicleInfoList(string rootID)
        {
            DataSet ds = new DataSet("Article");

            // 从数据库获取数据，放入 record 表
            string strSql = string.Format("EXEC GetVehicleInfoList '{0}'", rootID);//需要该接口
            SqlDataAdapter da = new SqlDataAdapter(strSql, ConnectionString);
            DataTable dtRecord = new DataTable("record");
            lock (da)
            {
                da.Fill(dtRecord);
            }
            ds.Tables.Add(dtRecord);

            // 计算总行数，放入 results 表
            DataTable dtResult = new DataTable("results");
            dtResult.Columns.Add("totalRecords");
            DataRow dr = dtResult.NewRow();
            return ds;
        }

        //餐饮
        public static DataSet getPlaceXQCY(string clientNo)
        {
            string url = string.Format("SELECT * FROM PLACE_KITCHEN WHERE CLIENT_NO LIKE '%{0}%'", clientNo);
            return GetData(url);
        }
        //处理厂
        public static DataSet getPlaceCLC(string clientNo)
        {
            string url = string.Format("SELECT * FROM PLACE_FACTORY WHERE CLIENT_NO LIKE '%{0}%'", clientNo);
            return GetData(url);
        }
        //中转站
        public static DataSet getPlaceZZZ(string clientNo)
        {
            string url = string.Format("SELECT * FROM PLACE_STATION WHERE CLIENT_NO LIKE '%{0}%'", clientNo);
            return GetData(url);
        }
        //小区厨余
        public static DataSet getPlaceCY(string clientNo)
        {
            string url = string.Format("SELECT * FROM PLACE_HOTEL WHERE CLIENT_NO LIKE '%{0}%'", clientNo);
            return GetData(url);
        }
        //农村点
        public static DataSet getPlaceNCD(string clientNo)
        {
            string url = string.Format("SELECT * FROM PLACE_POINT WHERE CLIENT_NO LIKE '%{0}%'", clientNo);
            return GetData(url);
        }
        //小区其他点
        public static DataSet getPlaceXQQT(string clientNo)
        {
            string url = string.Format("SELECT * FROM PLACE_OTHER WHERE CLIENT_NO LIKE '%{0}%'", clientNo);
            return GetData(url);
        }


        
        //public static DataSet getPlaceXQQT(string clientNo)
        public static DataSet GetVehicleListForAlarmQuery(string clientNo, string type)
        {
            DataSet ds = new DataSet("Article");

            // 从数据库获取数据，放入 record 表
            string strSql = string.Empty;

            if (type == "2")
            {
                //SELECT NAME FROM WORKER WHERE CLIENT_NO LIKE '0000004%'
                strSql = "SELECT NAME FROM WORKER WHERE CLIENT_NO LIKE '" + clientNo + "%' ";
            }
            if (type == "1")
            {
                strSql = "SELECT LICENSE_PLATE FROM VEHICLE A WHERE CLIENT_NO LIKE '" + clientNo + "%' AND NOT EXISTS(SELECT 1 FROM VEHICLE_RUBBISH WHERE VID=A.VID)";
            }
            if (type == "3")
            {
                strSql = "SELECT LICENSE_PLATE FROM VEHICLE_RUBBISH WHERE CLIENT_NO LIKE '" + clientNo + "%'";
            }
            if (type == "5")
            {
                strSql = "SELECT NAME FROM WORKER WHERE CLIENT_NO LIKE '" + clientNo + "%' "+ " UNION ALL "+
                    "SELECT LICENSE_PLATE FROM VEHICLE A WHERE CLIENT_NO LIKE '" + clientNo + "%' AND NOT EXISTS(SELECT 1 FROM VEHICLE_RUBBISH WHERE VID=A.VID)"+" UNION ALL "+ 
                    "SELECT LICENSE_PLATE FROM VEHICLE_RUBBISH WHERE CLIENT_NO LIKE '" + clientNo + "%'";
            }
            return GetData(strSql);
        }


        private static string dateFa(string date)
        {
            string[] sArray = date.Split('-');
            string dateNew = "";
            foreach (string i in sArray)
            {
                if (i.Length > 1)
                {
                    dateNew = dateNew + i;
                }
                else
                {
                    dateNew = dateNew + "0" + i;
                }
                dateNew = dateNew + "-";
            }
            return dateNew.Substring(0, dateNew.Length - 1);
        }


        //车辆详情 按处理厂 吨数统计
        public static DataSet GetVehicleWeightByFactory(string date, string name)
        {
            //exec R_GetVehicleWeightByFactory '2019-09-01','处理厂名'
            String url = string.Format("Exec R_GetVehicleWeightByFactory '{0}','{1}'", dateFa(date), name);
            //return GetData(url);
            SqlConnection cn = new SqlConnection(ConnectionString);
            SqlCommand cm = new SqlCommand(url, cn);

            cm.Connection.Open();

            SqlDataAdapter adapter = new SqlDataAdapter(cm);
            DataSet ds = new DataSet();
            adapter.Fill(ds);
            return ds;
        }

        //车辆详情 按区 吨数统计
        public static DataSet GetVehicleWeightByDistrict(string clientName, string date, string type, string name)
        {
            //EXEC R_GetVehicleWeightByDistrict '汉沽', '2019-08-01', '处理厂', ''
            String url = string.Format("Exec R_GetVehicleWeightByDistrict '{0}','{1}','{2}','{3}'", clientName, dateFa(date), type, name);
            //return GetData(url);
            SqlConnection cn = new SqlConnection(ConnectionString);
            SqlCommand cm = new SqlCommand(url, cn);

            cm.Connection.Open();

            SqlDataAdapter adapter = new SqlDataAdapter(cm);
            DataSet ds = new DataSet();
            adapter.Fill(ds);
            return ds;
        }

        //车辆详情 吨数统计
        public static DataSet GetVehicleWeightByFactoryAndDistrictBh(string clientName, string date, string name)
        {
            //EXEC R_GetVehicleWeightByFactoryAndDistrictBh '汉沽', '2019-08-01','天津滨海新区垃圾焚烧发电厂'
            String url = string.Format("Exec R_GetVehicleWeightByFactoryAndDistrictBh '{0}','{1}','{2}'", clientName, dateFa(date), name);
            //return GetData(url);
            SqlConnection cn = new SqlConnection(ConnectionString);
            SqlCommand cm = new SqlCommand(url, cn);

            cm.Connection.Open();

            SqlDataAdapter adapter = new SqlDataAdapter(cm);
            DataSet ds = new DataSet();
            adapter.Fill(ds);
            return ds;
        }

        //按区获取中转站、处理厂 吨数统计
        public static DataSet GetDistrictWeightAllBh(string date, string type, string typeLJ)
        {
            if (typeLJ == "全部")
            {
                typeLJ = "";
            }
            //EXEC R_GetDistrictWeightAllBh '2019-08-01', '处理厂',''//处理厂类型
            String url = string.Format("Exec R_GetDistrictWeightAllBh '{0}','{1}','{2}'", date, type, typeLJ);
            //return GetData(url);
            SqlConnection cn = new SqlConnection(ConnectionString);
            SqlCommand cm = new SqlCommand(url, cn);

            cm.Connection.Open();

            SqlDataAdapter adapter = new SqlDataAdapter(cm);
            DataSet ds = new DataSet();
            adapter.Fill(ds);
            return ds;
        }

        public static DataSet GetDistrictWeightByFactoryBh(string date, string nameFactory)
        {
            //EXEC R_GetDistrictWeightByFactoryBh '2019-08-01','天津滨海新区垃圾焚烧发电厂'
            String url = string.Format("Exec R_GetDistrictWeightByFactoryBh '{0}','{1}'", dateFa(date), nameFactory);
            //return GetData(url);
            SqlConnection cn = new SqlConnection(ConnectionString);
            SqlCommand cm = new SqlCommand(url, cn);

            cm.Connection.Open();

            SqlDataAdapter adapter = new SqlDataAdapter(cm);
            DataSet ds = new DataSet();
            adapter.Fill(ds);
            return ds;
        }

        //按站名中转站、处理厂 吨数统计
        public static DataSet GetFactoryWeightAllBh(string date, string name)
        {
            //EXEC R_GetFactoryWeightAllBh '2019-08-01', ''//处理厂类型
            String url = string.Format("Exec R_GetFactoryWeightAllBh '{0}','{1}'", date, name);
            //return GetData(url);
            SqlConnection cn = new SqlConnection(ConnectionString);
            SqlCommand cm = new SqlCommand(url, cn);

            cm.Connection.Open();

            SqlDataAdapter adapter = new SqlDataAdapter(cm);
            DataSet ds = new DataSet();
            adapter.Fill(ds);
            return ds;
        }

        public static DataSet GetFactoryWeightByDistrict(string clientName, string date, string type, string typeFactory)
        {
            //exec R_GetDistrictWeight  '塘沽','2019-08-01','处理厂',''
            String url = string.Format("Exec R_GetDistrictWeight '{0}','{1}','{2}','{3}'", clientName, dateFa(date), type, typeFactory);
            //return GetData(url);
            SqlConnection cn = new SqlConnection(ConnectionString);
            SqlCommand cm = new SqlCommand(url, cn);

            cm.Connection.Open();

            SqlDataAdapter adapter = new SqlDataAdapter(cm);
            DataSet ds = new DataSet();
            adapter.Fill(ds);
            return ds;
        }

        private static string cnStr = string.Empty;
        public static string ConnectionString
        {
            get
            {
                if (cnStr == string.Empty)
                {
                    cnStr = ConfigurationManager.ConnectionStrings["CONN"].ConnectionString;
                }
                return cnStr;
            }

        }

        public static string GetConnection()
        {
            return ConfigurationManager.ConnectionStrings["CONN"].ConnectionString;
        }

        public static DataSet GetData(string sql, string resultTableName)
        {
            DataSet ds = new DataSet("DataSet" + resultTableName);
            SqlDataAdapter da = new SqlDataAdapter(sql, ConnectionString);
            DataTable dtRecord = new DataTable(resultTableName);
            lock (da)
            {
                da.SelectCommand.CommandTimeout = 1800;
                da.Fill(dtRecord);
            }
            ds.Tables.Add(dtRecord);
            return ds;
        }

        public static DataSet GetData(string sql)
        {
            return GetData(sql, "Result");
        }

        public static int SetData(string sql)
        {
            int t_count = 0;
            using (SqlConnection cn = new SqlConnection(GetConnection()))
            {
                try
                {
                    cn.Open();

                    using (SqlCommand cm = new SqlCommand(sql, cn))
                    {
                        t_count = cm.ExecuteNonQuery();
                    }
                }
                catch (SqlException sqlEx)
                {
                    throw new Exception(sqlEx.Message);
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
                finally
                {
                    cn.Close();
                }

            }
            return t_count;
        }

        public static LngLat LngLatOffset(LngLat position)
        {
            LngLat lngLat = new LngLat();
            DataTable dt = Services.GetData(string.Format("SELECT {0}+LngOffset LngOffset,{1}+LatOffset LatOffset FROM GoogleMapOffset WHERE Lng=ROUND({0},1) AND Lat=ROUND({1},1)", position.Lng, position.Lat)).Tables[0];
            if (dt.Rows.Count > 0)
            {
                lngLat.Lng = double.Parse(dt.Rows[0]["LngOffset"].ToString());
                lngLat.Lat = double.Parse(dt.Rows[0]["LatOffset"].ToString());
            }
            else
                return position;
            return lngLat;
        }

        public static LngLat LngLatOffset(double lng, double lat)
        {
            return LngLatOffset(new LngLat() { Lng = lng, Lat = lat });
        }

        /// <summary>
        /// 向Socket服务器发送消息
        /// </summary>
        /// <param name="simcard">目标号码</param>
        /// <param name="msg">消息内容</param>
        /// <returns></returns>
        public static string SendMessage(string simcard, string content)
        {
            try
            {
                SendToServer(simcard, content);
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
            return "OK";
        }

        private static bool SendToServer(string simcard, string msg)
        {
            StringBuilder sendContent = new StringBuilder();
            sendContent.Append("<CMD_HANDOUT>");
            sendContent.Append("\n");
            sendContent.Append("<DEST>");
            sendContent.Append(simcard);
            sendContent.Append("</DEST>");
            sendContent.Append("\n");
            sendContent.Append("<CONTENT>");
            sendContent.Append(msg.Length.ToString("000"));
            sendContent.Append(msg);
            sendContent.Append("</CONTENT>");
            sendContent.Append("\n");
            sendContent.Append("</CMD_HANDOUT>");
            sendContent.Append("\n");
            msg = sendContent.ToString();

            Socket mySocket = new Socket(AddressFamily.InterNetwork, SocketType.Stream, ProtocolType.Tcp);
            try
            {
                IPAddress ipAddress = IPAddress.Parse(ConfigurationManager.AppSettings["ServerIP"]);
                IPEndPoint ipEndPoint = new IPEndPoint(ipAddress, int.Parse(ConfigurationManager.AppSettings["Port"]));
                mySocket.Connect(ipEndPoint);
            }
            catch (System.Exception ex)
            {
                throw new Exception(ex.Message);
            }
            try
            {
                byte[] byteArray = Encoding.Default.GetBytes(msg);
                mySocket.Send(byteArray, 0, byteArray.Length, SocketFlags.None);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                mySocket.Close();
            }
            return true;
        }

        public static DataSet GetArticles(int start, int limit, string sort, string dir)
        {
            DataSet ds = new DataSet("Article");

            // 从数据库获取数据，放入 record 表
            string strSql = string.Format(
                "select top {0} * from Article where Id not in (select top {1} Id from Article order by {2} {3}) order by {2} {3}",
                limit, start, sort, dir);
            SqlDataAdapter da = new SqlDataAdapter(strSql, ConnectionString);
            DataTable dtRecord = new DataTable("record");
            lock (da)
            {
                da.Fill(dtRecord);
            }
            ds.Tables.Add(dtRecord);

            // 计算总行数，放入 results 表
            DataTable dtResult = new DataTable("results");
            dtResult.Columns.Add("totalRecords");
            DataRow dr = dtResult.NewRow();

            using (SqlConnection conn = new SqlConnection(ConnectionString))
            using (SqlCommand cmd = new SqlCommand("select count(*) from Article", conn))
            {
                try
                {
                    conn.Open();
                    dr["totalRecords"] = (int)cmd.ExecuteScalar();
                }
                catch
                {
                    // do nothing
                }
            }
            dtResult.Rows.Add(dr);
            ds.Tables.Add(dtResult);

            return ds;
        }

        public static UserInfo Login(string userName, string password)
        {
            UserInfo userInfo = null;
            using (SqlConnection cn = new SqlConnection(ConnectionString))
            {
                try
                {
                    cn.Open();
                    using (SqlCommand cm = cn.CreateCommand())
                    {
                        cm.CommandType = CommandType.StoredProcedure;
                        cm.CommandText = "Login";
                        cm.Parameters.AddWithValue("@UserName", userName);
                        cm.Parameters.AddWithValue("@Password", password);
                        using (SqlDataReader dr = cm.ExecuteReader())
                        {
                            if (dr.Read())
                            {
                                userInfo = new UserInfo();
                                userInfo.UserName = dr.IsDBNull(0) ? "" : dr.GetString(0);
                                userInfo.ClientNo = dr.IsDBNull(1) ? "" : dr.GetString(1);
                                userInfo.RoleName = dr.IsDBNull(2) ? "" : dr.GetString(2);
                                userInfo.ClientName = dr.IsDBNull(3) ? "" : dr.GetString(3);
                                userInfo.Password = password;
                                //userInfo.TelePhone = dr.IsDBNull(5) ? "" : dr.GetString(5);
                                return userInfo;
                            }
                            dr.Close();
                        }
                    }
                }
                catch (SqlException sqlEx)
                {
                    throw new Exception(sqlEx.Message);
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
                finally
                {
                    cn.Close();
                }
            }
            return userInfo;
        }

        public static bool ChangPassword(string userName, string password)
        {
            using (SqlConnection cn = new SqlConnection(ConnectionString))
            {
                try
                {
                    cn.Open();
                    using (SqlCommand cm = cn.CreateCommand())
                    {
                        cm.CommandType = CommandType.Text;
                        cm.CommandText = string.Format("Exec ChangPassword '{0}',{1}", userName, password);
                        cm.ExecuteNonQuery();
                    }
                }
                catch (SqlException sqlEx)
                {
                    throw new Exception(sqlEx.Message);
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
                finally
                {
                    cn.Close();
                }
            }
            return true;
        }



    }
}